<?php
class Admin_Model_Report extends My_Model_Db
{
    public function deleteReleve()
    {
        $sql = 'delete from releve where montant = 0';
        $this->getAdapter()->query($sql);
        return $this;
    }

    public function getClientsCollection($numVente)
    {
        $select = $this->getAdapter()->select()
        ->from('clients', array('prenom', 'nom', 'vendeur', 'p_clients'))
        ->join('releve', 'releve.f_clients_acheteur = clients.p_clients', array('num', 'p_releve', 'montant'))
        ->join('clients_ventes', 'clients_ventes.f_clients = releve.f_clients_acheteur', array('num_client'))
        ->where('releve.f_ventes = ?', $numVente)
        ->where('clients_ventes.f_ventes = ?', $numVente)
        ->where('releve.montant > ?', 0)
        ->group('p_releve')
        ->order(array('num'));
        return $select;

        //return $this->getAdapter()->fetchAll($select);
    }

    public function getClient($releveId)
    {
        $select = $this->getAdapter()->select()
        ->from('clients', array('p_clients', 'prenom', 'nom', 'cp', 'adresse', 'ville', 'co', ))
        ->join('pays', 'clients.f_pays = pays.p_pays', array('pays' => 'nom'))
        ->join('releve', 'releve.f_clients_acheteur = clients.p_clients', array('num'))
        ->where('releve.p_releve = ?', $releveId);

        return $this->getAdapter()->fetchRow($select);
    }

    public function getMontantAcompte($releveId)
    {
        $select = $this->getAdapter()->select()
        ->from('releve_acompte', array('acompte' => 'SUM(montant)'))
        ->where('f_releve = ?', $releveId);
        return $this->getAdapter()->fetchOne($select);
    }

    public function countReleveAcompte($releveId)
    {
        $select = $this->getAdapter()->select()
        ->from('releve_acompte')
        ->where('f_releve = ?', $releveId);
        return $this->getAdapter()->query($select)->rowCount();
    }

    public function countDeposant($clientId, $numVente)
    {
        $select = $this->getAdapter()->select()
        ->from('articles', array('p_articles'))
        ->where('f_clients_deposant = ?', $clientId)
        ->where('deleted = ?', 0)
        ->where('f_ventes = ?', $numVente);
        return $this->getAdapter()->query($select)->rowCount();
    }

    public function repairReleve($numVente, $fraisCommAcheteur)
    {
        $adapter = $this->getAdapter();
        $select = $adapter->select()
        ->from('articles', array('DISTINCT(f_clients_acheteur)'))
        ->where('adjuge > ?', 0)
        ->where('f_clients_acheteur > ?', 0)
        ->where('f_ventes = ?', $numVente);
        $result = $adapter->fetchAll($select);
        if ($result) {
            foreach ($result as $item) {
                $numClient = $item['f_clients_acheteur'];
                $this->calculateReleve($numClient, $numVente);
            }
        }
        return $this;
    }

    public function calculateReleve($numClient, $numVente)
    {
        $adapter = $this->getAdapter();
        $select = $adapter->select()->from('releve', array('p_releve'))
        ->where('f_clients_acheteur = ?', $numClient)
        ->where('f_ventes = ?', $numVente)->order(array('num DESC'));
        $releveId = $adapter->fetchOne($select);
        if (!$releveId) {
            $select = $adapter->select()->from('releve', array('lemax' => 'MAX(num)'))
            ->where('f_ventes = ?', $numVente);
            $maxLemax = $adapter->fetchOne($select);
            $numReleve = $maxLemax ? $maxLemax + 1 : 1;
            $data = array(
                'num' => $numReleve,
                'f_clients_acheteur' => $numClient,
                'montant' => 0,
                'f_ventes' => $numVente
            );
            $releveId = $adapter->insert('releve', $data);
        } else {
            $select = $adapter->select()->from('releve', array('montant'))
            ->where('p_releve = ?', $releveId);
            $montantAPayer = $adapter->fetchOne($select);
            //echo $montantAPayer;
            $select = $adapter->select()->from('releve_acompte', array('total' => 'SUM(montant)'))
            ->where('f_releve = ?', $releveId);
            $montantPayer = $adapter->fetchOne($select);
            if (!$montantPayer) {
                $montantPayer = 0;
            }
            //echo $montantAPayer . '---' . $montantPayer; exit;
            if ($montantPayer == $montantAPayer && $montantAPayer > 0) {
                $select = $adapter->select()->from('releve', array(''))
                ->join('releve_detail', 'releve_detail.f_releve = releve.p_releve', array('f_articles'))
                ->where('releve.f_clients_acheteur = ?', $numClient);

                //echo $select->__toString(); exit;

                $select = $adapter->select()->from('articles', array('p_articles'))
                ->where('adjuge > ?', 0)
                ->where('f_ventes = ?', $numVente)
                ->where('f_clients_acheteur = ?', $numClient)
                ->where('p_articles NOT IN (?)', $select->__toString());

                $rowCount = $adapter->query($select)->rowCount();
                if ($rowCount > 0) {
                    $select = $adapter->select()->from('releve', array('lemax' => 'MAX(num)'))
                    ->where('f_ventes = ?', $numVente);
                    $maxLemax = $adapter->fetchOne($select);
                    $numReleve = $maxLemax ? $maxLemax + 1 : 1;
                    $data = array(
                        'num' => $numReleve,
                        'f_clients_acheteur' => $numClient,
                        'montant' => 0,
                        'f_ventes' => $numVente
                    );
                    $releveId = $adapter->insert('releve', $data);
                }
            }
        }
        $select = $adapter->select()->from('articles', array('p_articles'))
        ->where('adjuge > ?', 0)
        ->where('f_ventes = ?', $numVente)
        ->where('f_clients_acheteur = ?', $numClient);

        $rows = $adapter->fetchAll($select);
        if ($rows) {
            foreach ($rows as $row) {
                $select = $adapter->select()->from('releve_detail', array('p_releve_detail'))->where('f_articles = ?', $row['p_articles']);
                if ($adapter->query($select)->rowCount() == 0) {
                    $data = array(
                        'f_articles' => $row['p_articles'],
                        'f_releve' => $releveId,
                    );
                    $adapter->insert('releve_detail', $data);
                }
            }
        }

        $select = $adapter->select()->from('articles', array('num_article', 'droit_suite', 'adjuge'))
        ->join('releve_detail', 'releve_detail.f_articles = articles.p_articles', array())
        ->where('releve_detail.f_releve = ?', $releveId);

        $totAjugde = 0;
        $suite = 0;
        $rows = $adapter->fetchAll($select);
        if ($rows) {
            foreach ($rows as $row) {
                $totAjugde += $row['adjuge'];
                if ($row['droit_suite'] == 1) {
                    $suite += self::suite($row['adjuge']);
                }
            }
        }
        $frais = ($totAjugde * $fraisCommAcheteur) / 100;
        $lots = count($rows) * 2;
        $grandTotal = $totAjugde + $suite + $frais + $lots;
        $data = array('montant' => str_replace(",", ".", $grandTotal));
        $where = $adapter->quoteInto('p_releve = ?', $releveId);
        $adapter->update('releve', $data, $where);
    }

    public static function suite($montant)
    {
        $result = 0;
        // On vérifie si le montant est >= 2000 EUR
        if ($montant >= 2000) {
            if ($montant <= 50000) {
                $result = $montant * 0.04;
            } elseif ($montant > 50000 && $montant <= 200000) {
                $result = (50000 * 0.04) + (($montant - 50000) * 0.03);
            } elseif ($montant > 200000 && $montant <= 350000) {
                $result = (50000 * 0.04) + (150000 * 0.03) + (($montant - 200000) * 0.01);
            } elseif ($montant > 350000 && $montant <= 500000) {
                $result = (50000 * 0.04) + (150000 * 0.03) + (150000 * 0.01) + (($montant - 350000) * 0.005);
            } elseif ($montant > 500000) {
                $result = (50000 * 0.04) + (150000 * 0.03) + (150000 * 0.01) + (150000 * 0.005) + (($montant - 500000) * 0.0025);
            }
        }
        return $result;
    }
}